/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package Project2.cs342.project1.mathNotebook.Database;

import java.sql.*;
import java.util.*;
/**
 *
 * @author Rick
 */
public class DBGroupMembers {
    static String id = null;
    static String file_name = null;
    static Connection conn = null;
    PreparedStatement ps = null;
    SQLConfigeration sqlConfig;

    public DBGroupMembers(){
        sqlConfig = new SQLConfigeration();
        conn = sqlConfig.getConnection();
    }

    /**
     * inserts a user into the given group
     *
     * @param groupName the group that the user is to be associated with
     * @param username the user to be added
     */
    public void insertIntoGroupMembers(String groupName, String username, String accessLevel){

        //Insert data
        String groupAccess = "insert into GroupMembers(groupname,username,accessLevel)" +
                                                    "values (?,?,?)";


        try{
            conn.setAutoCommit(false);

            ps = conn.prepareStatement(groupAccess);

            ps.setString(1, groupName);
            ps.setString(2, username);
            ps.setString(3, accessLevel);
            System.out.println("Attempting insert");
            ps.executeUpdate();
            conn.commit();
            System.out.println("Insert successful");

        }catch(Exception ex){
            System.out.println("Insert Error " + ex + " at:");
            ex.printStackTrace();
        }finally{
            try {
                ps.close();

            } catch (SQLException ex) {
                System.out.println("Sql Error " + ex);
            }
        }
    }

    /**
     * Retrieves the Users associated with the provided group
     *
     * @param groupName the group that the users are a part of
     * @return an array of users associated to the provided group
     */
    public String[] retrieveUsersInGroup(String groupName){
        String[] users = new String[50];
        ResultSet rs = null;
        PreparedStatement pstmt = null;
        String query = "Select username from GroupMembers where username IS NOT NULL AND groupName = ?";

        try{
            pstmt = conn.prepareStatement(query);

            pstmt.setString(1, groupName);
            rs = pstmt.executeQuery();

            //get the binary blob data onto the client
            System.out.println("Attempting to Users in group");
            int index = 0;
            while(rs.next()){
                users[index] = rs.getString("username");
                //System.out.println("Recieved: " + filename[index]);
                index++;
            }

        }catch(Exception ex){System.out.println("Retrieval Error: " + ex);}
        finally{
            try {
                rs.close();
                pstmt.close();
                conn.close();
            } catch (SQLException ex) {
                System.out.println("SQL Error: " + ex);
            }
        }
        return users;
    }

    /**
     * Retrieves the groups associated with a given user
     *
     * @param userName the user that a group is associated with
     * @return an array of groups that a given user is a part of
     */
    public String[] retrieveGroupsForUser(String userName){
        String[] groups = new String[50];

        ResultSet rs = null;
        PreparedStatement pstmt = null;
        String query = "Select groupname from GroupMembers where groupname IS NOT NULL AND username = ?";

        try{
            pstmt = conn.prepareStatement(query);

            pstmt.setString(1, userName);
            rs = pstmt.executeQuery();

            //get the binary blob data onto the client
            System.out.println("Attempting to get Groups");
            int index = 0;
            while(rs.next()){
                groups[index] = rs.getString("groupname");
                //System.out.println("Recieved: " + filename[index]);
                index++;
            }

        }catch(Exception ex){System.out.println("Retrieval Error: " + ex);}
        finally{
            try {
                rs.close();
                pstmt.close();
                conn.close();
            } catch (SQLException ex) {
                System.out.println("SQL Error: " + ex);
            }
        }
        ArrayList<String> list = new ArrayList<String>();
        for (int i = 0; i < groups.length;i++){
            if (groups[i]!=null){
                list.add(groups[i]);
                System.out.println(groups[i]);
            }
        }
        String[] groups2 = new String[list.size()];
        list.toArray(groups2);
        return groups2;
    }

    /**
     * Deletes a specified user from the provided group
     *
     * @param groupName group to be deleted from
     * @param userName user to be deleted
     */
    public void deleteRowFromGroupMembers(String groupName, String userName){
        String deleteGroupMember = "delete from GroupMembers where groupName = '" +
                                                    groupName + "' and username = '" +
                                                    userName + "'";
        try{
            conn.setAutoCommit(false);
            ps = conn.prepareStatement(deleteGroupMember);
            System.out.println("Attempting Deletion");
            ps.executeUpdate();
            conn.commit();
            System.out.println("Delection Successful");

        }catch(SQLException ex){
            System.out.println("Deletion Error: " + ex);
        }

    }
    public String getAccessLevel(String username, String groupname){
        
        
        
        ResultSet rs = null;
        PreparedStatement pstmt = null;
        String query = "Select accesslevel from GroupMembers where username = '?' AND groupName = '?'";
        String accessLevel  = "";
        try{
            pstmt = conn.prepareStatement(query);

            pstmt.setString(1, username);
            pstmt.setString(2, groupname);
            rs = pstmt.executeQuery();
            rs.next();
            accessLevel = rs.getString("accesslevel");
        }catch(SQLException ex){
            System.out.println("Access Level Error: " + ex);
        }
        return accessLevel;
    }
    public String[] addAccessLevel(String[] usernames, String groupname){



        ResultSet rs = null;
        PreparedStatement pstmt = null;

        String accessLevel  = "";

        try{



            for(int i = 0;i<usernames.length;i++){
            String query = "Select accesslevel from GroupMembers where username = '?' AND groupName = '?'";
            pstmt = conn.prepareStatement(query);
            pstmt.setString(1, usernames[i]);
            pstmt.setString(2, groupname);
            rs = pstmt.executeQuery();
            System.out.println("before next");
            rs.next();
            System.out.println("after next");
            accessLevel = rs.getString("accesslevel");
            usernames[i] += (" " + accessLevel);
            System.out.println(usernames[i] + " is set");
            }
        }catch(SQLException ex){
            System.out.println("Access Level Error: " + ex);
        }
        return usernames;
    }


}
